






ALTER        proc sp_update_open_order_line_item_status
@customer_shipping_id int,
@status varchar(20),
@userId varchar(50)
AS
begin
	declare @temp_line_item_id int
	declare @temp_qty_requested int
	declare @temp_total_qty_shipped int
	declare @temp_cool_line_item_no varchar(10)
	declare @temp_salesorder_id int
	declare @temp_action varchar(100)


	declare lineItemCursor CURSOR FOR

	select  LineItem_ID from Customer_Shipping_Line_Item_Table		      
 				      where CustomerShipping_ID = @customer_shipping_id 	
	open lineItemCursor 
	fetch next from lineItemCursor into @temp_line_item_id
	while @@fetch_status = 0		
	begin
		select @temp_total_qty_shipped = SUM(CSLIT_Qty_Shipped) FROM Customer_Shipping_Line_Item_Table 
			Where LineItem_ID = @temp_line_item_id
			and  CSLIT_Status = 'SHIPPED' 
			group by LineItem_ID
	
		select @temp_qty_requested = COOL_Qty from customer_open_order_lineItem Where LineItem_ID = @temp_line_item_id
			
		if( @temp_qty_requested = @temp_total_qty_shipped )
		begin
		  update Customer_Open_Order_LineItem set COOL_Status = @status where LineItem_ID = @temp_line_item_id
		  SET @temp_action = 'Shipped'
		end
		else
		begin
		 SET @temp_action = 'Partially shipped'
		end
		select @temp_salesorder_id=SalesOrder_ID, @temp_cool_line_item_no=COOL_Line_Item_No from Customer_Open_Order_LineItem where LineItem_ID = @temp_line_item_id
		SET @temp_action =  '<br> Line Item No: '+ @temp_cool_line_item_no+' is '+@temp_action
                insert into Sales_Audit_Trail(SalesOrder_ID, User_ID, Actions, Comments, Timestamp) values (@temp_salesorder_id,@userId,@temp_action,NULL,CURRENT_TIMESTAMP)
		fetch next from lineItemCursor into @temp_line_item_id 
	end
	close lineItemCursor
	deallocate lineItemCursor

end





